<?php
/**
 * 产品控制器
 * @author 聂哥
 * @Date: 2023-03-06 10:46:45
*/
namespace app\admin\controller\supply;

use app\common\controller\SupplyController;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as WriterXlsx;
use think\Db;
use think\Exception;

class Quoted extends SupplyController
{
    //protected $validate = 'Banner';
    protected $model = 'SupplyQuoted';


    /**
     * 批量导入
    */
    public function import()
    {
        if ($this->request->isPost()) {
            $post = $this->request->post();

            if( empty($post['excel']) ){
                return $this->error('excel文件不能为空');
            }
            $reader = IOFactory::createReader('Xlsx');
            $reader->setReadDataOnly(TRUE);
            $spreadsheet = $reader->load(substr($post['excel'],1)); //载入excel表格

            $worksheet = $spreadsheet->getActiveSheet();
            $highestRow = $worksheet->getHighestRow(); // 总行数
            $highestColumn = $worksheet->getHighestColumn(); // 总列数
            $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // e.g. 5

            $lines = $highestRow - 2;
            if ($lines <= 0) {
                exit('Excel表格中没有数据');
            }

            $sql = "INSERT INTO `yd_supply_quoted` (`product_id`, `specs`, `price`, `company_id`, `admin_id`,`create_time`) VALUES ";

            for ($row = 2; $row <= $highestRow; ++$row) {
                $product_id = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //产品id
                $specs = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //规格
                $price = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //价格
                $company_id = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //厂家id
                $admin_id = $worksheet->getCellByColumnAndRow(5, $row)->getValue(); //用户id
                //$create_time = $worksheet->getCellByColumnAndRow(5, $row)->getValue(); //用户id

                $create_time = time();
                $sql .= "('$product_id','$specs','$price','$company_id','$admin_id','$create_time'),";
            }
            $sql = rtrim($sql, ","); //去掉最后一个,号
            try {
                Db::query($sql);
                $this->success('导入成功');
            } catch (Exception $e) {
                $this->error($e->getMessage());  
            }
        } 
        return $this->fetch();
    }

    /**
     * 模板生成
    */
    public function create_xlsx()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Welcome to Helloweba.');
        
        $writer = new WriterXlsx($spreadsheet);
        $writer->save('category.xlsx');
    }
}
